import logging
from datetime import datetime, timedelta
from pymysqlreplication import BinLogStreamReader
from pymysqlreplication.row_event import (
    DeleteRowsEvent,
    UpdateRowsEvent,
    WriteRowsEvent,
)
from django.conf import settings
from elasticsearch import Elasticsearch
from .course_sync import CourseSync

# logging.basicConfig(level=logging.INFO)
logger = logging.getLogger('django')

class BinlogSync:
    def __init__(self):
        # MySQL配置
        self.mysql_settings = {
            'host': settings.DATABASES['default']['HOST'],
            'port': int(settings.DATABASES['default'].get('PORT', 3306)),
            'user': settings.DATABASES['default']['USER'],
            'passwd': settings.DATABASES['default']['PASSWORD'],
        }
        
        # 健康检查
        self.last_event_time = datetime.now()
        self.health_check_interval = 60  # 60秒检查一次
        
        # 需要监听的表
        self.tables = {
            'tb_course': self.handle_course_change,
            'tb_teacher': self.handle_teacher_change,
            'tb_course_tag': self.handle_tag_change,
            'tb_course_tag_relation': self.handle_tag_relation_change,
            'tb_course_nav_relation': self.handle_nav_relation_change,
            'tb_course_category': self.handle_category_change,
            'tb_nav_category': self.handle_nav_category_change,
        }
        
        self.course_sync = CourseSync()
        self.es = Elasticsearch(['http://localhost:9200'])
        
        # 数据库名
        self.database = settings.DATABASES['default']['NAME']
        logger.info(f"Using database: {self.database}")

    def start_sync(self):
        """启动binlog同步"""
        try:
            # 配置binlog stream reader
            stream = BinLogStreamReader(
                connection_settings=self.mysql_settings,
                server_id=100,  # 唯一的server id
                only_events=[DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent],
                only_tables=list(self.tables.keys()),
                only_schemas=[self.database],
                resume_stream=True,
                blocking=True
            )

            logger.info("Started binlog sync...")
            
            for binlogevent in stream:
                # 获取事件的表名
                table = binlogevent.table
                
                if table in self.tables:
                    # 更新最后事件时间
                    self.last_event_time = datetime.now()
                    
                    # 健康检查
                    if (datetime.now() - self.last_event_time).seconds >= self.health_check_interval:
                        logger.info("Health check: Binlog sync is running")
                        logger.info(f"Last event received: {self.last_event_time}")
                    
                    event_type = type(binlogevent).__name__
                    logger.info(f"Processing {event_type} for table {table}")
                    
                    if isinstance(binlogevent, DeleteRowsEvent):
                        for row in binlogevent.rows:
                            logger.debug(f"Delete event: {row['values']}")
                            self.tables[table](row["values"], 'delete')
                    
                    elif isinstance(binlogevent, UpdateRowsEvent):
                        for row in binlogevent.rows:
                            logger.debug(f"Update event: {row['after_values']}")
                            self.tables[table](row["after_values"], 'update')
                    
                    elif isinstance(binlogevent, WriteRowsEvent):
                        for row in binlogevent.rows:
                            logger.debug(f"Insert event: {row['values']}")
                            self.tables[table](row["values"], 'insert')

        except Exception as e:
            logger.error(f"Binlog sync error: {str(e)}")
            raise
        finally:
            stream.close()

    def handle_course_change(self, row_data, event_type):
        """处理课程变更"""
        try:
            logger.info(f"Handling course change for event type: {event_type}")
            logger.info(f"Row data: {row_data}")

            course_id = row_data['id']
            logger.info(f"Processing course {course_id}")

            if event_type == 'delete':
                # 从ES中删除课程
                self.es.delete(index='course_index_alias', id=course_id, ignore=[404])
                logger.info(f"Deleted course {course_id} from ES")
            else:
                # 更新或插入课程到ES
                logger.info(f"Syncing course {course_id} to ES")
                self.course_sync.sync_single_course(course_id)
                logger.info(f"Synced course {course_id} to ES")
        except Exception as e:
            logger.error(f"Error handling course change: {str(e)}")

    def handle_teacher_change(self, row_data, event_type):
        """处理讲师变更"""
        conn = None
        cursor = None
        try:
            teacher_id = row_data['id']
            # 获取该讲师关联的所有课程
            conn = self.course_sync.get_mysql_connection()
            cursor = conn.cursor()
            cursor.execute("SELECT id FROM tb_course WHERE teacher_id = %s", (teacher_id,))
            course_ids = cursor.fetchall()
            
            for (course_id,) in course_ids:
                self.course_sync.sync_single_course(course_id)
            
            logger.info(f"Synced courses for teacher {teacher_id}")
        except Exception as e:
            logger.error(f"Error handling teacher change: {str(e)}")
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()

    def handle_tag_change(self, row_data, event_type):
        """处理标签变更"""
        conn = None
        cursor = None
        try:
            tag_id = row_data['id']
            # 获取使用该标签的所有课程
            conn = self.course_sync.get_mysql_connection()
            cursor = conn.cursor()
            cursor.execute("""
                SELECT course_id FROM tb_course_tag_relation 
                WHERE tag_id = %s
            """, (tag_id,))
            course_ids = cursor.fetchall()
            
            for (course_id,) in course_ids:
                self.course_sync.sync_single_course(course_id)
            
            logger.info(f"Synced courses for tag {tag_id}")
        except Exception as e:
            logger.error(f"Error handling tag change: {str(e)}")
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()

    def handle_tag_relation_change(self, row_data, event_type):
        """处理标签关系变更"""
        try:
            course_id = row_data['course_id']
            self.course_sync.sync_single_course(course_id)
            logger.info(f"Synced course {course_id} for tag relation change")
        except Exception as e:
            logger.error(f"Error handling tag relation change: {str(e)}")

    def handle_nav_relation_change(self, row_data, event_type):
        """处理导航关系变更"""
        try:
            course_id = row_data['course_id']
            if event_type == 'delete':
                # 如果是删除关系，我们仍然需要同步课程，因为它的导航分类发生了变化
                self.course_sync.sync_single_course(course_id)
                logger.info(f"Synced course {course_id} after nav relation deletion")
            else:
                self.course_sync.sync_single_course(course_id)
                logger.info(f"Synced course {course_id} for nav relation change")
        except Exception as e:
            logger.error(f"Error handling nav relation change: {str(e)}")

    def handle_nav_category_change(self, row_data, event_type):
        """处理导航分类变更"""
        conn = None
        cursor = None
        try:
            nav_category_id = row_data['id']
            
            if event_type == 'delete':
                # 如果是删除操作，我们需要先获取受影响的课程，然后再删除导航分类
                conn = self.course_sync.get_mysql_connection()
                cursor = conn.cursor()
                cursor.execute("""
                    SELECT DISTINCT c.id 
                    FROM tb_course c
                    INNER JOIN tb_course_nav_relation cnr ON c.id = cnr.course_id
                    WHERE cnr.nav_category_id = %s
                """, (nav_category_id,))
                affected_courses = cursor.fetchall()
                
                # 关闭当前连接，因为删除操作可能会影响到查询结果
                cursor.close()
                conn.close()
                
                # 重新同步受影响的课程
                for (course_id,) in affected_courses:
                    self.course_sync.sync_single_course(course_id)
                
                logger.info(f"Re-synced {len(affected_courses)} courses after nav category {nav_category_id} deletion")
            else:
                # 对于更新和插入操作
                conn = self.course_sync.get_mysql_connection()
                cursor = conn.cursor()
                cursor.execute("""
                    SELECT DISTINCT c.id 
                    FROM tb_course c
                    INNER JOIN tb_course_nav_relation cnr ON c.id = cnr.course_id
                    WHERE cnr.nav_category_id = %s
                """, (nav_category_id,))
                course_ids = cursor.fetchall()
                
                for (course_id,) in course_ids:
                    self.course_sync.sync_single_course(course_id)
                
                logger.info(f"Synced {len(course_ids)} courses for nav category {nav_category_id}")
        except Exception as e:
            logger.error(f"Error handling nav category change: {str(e)}")
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()

    def handle_category_change(self, row_data, event_type):
        """处理分类变更"""
        conn = None
        cursor = None
        try:
            category_id = row_data['id']
            
            if event_type == 'delete':
                # 如果是删除操作，我们需要先获取受影响的课程，然后再删除分类
                conn = self.course_sync.get_mysql_connection()
                cursor = conn.cursor()
                cursor.execute("""
                    SELECT DISTINCT c.id 
                    FROM tb_course c
                    INNER JOIN tb_course_categories cc ON c.id = cc.course_id
                    WHERE cc.category_id = %s
                """, (category_id,))
                affected_courses = cursor.fetchall()
                
                # 关闭当前连接，因为删除操作可能会影响到查询结果
                cursor.close()
                conn.close()
                
                # 重新同步受影响的课程
                for (course_id,) in affected_courses:
                    self.course_sync.sync_single_course(course_id)
                
                logger.info(f"Re-synced {len(affected_courses)} courses after category {category_id} deletion")
            else:
                # 对于更新和插入操作
                conn = self.course_sync.get_mysql_connection()
                cursor = conn.cursor()
                cursor.execute("""
                    SELECT DISTINCT c.id 
                    FROM tb_course c
                    INNER JOIN tb_course_categories cc ON c.id = cc.course_id
                    WHERE cc.category_id = %s
                """, (category_id,))
                course_ids = cursor.fetchall()
                
                for (course_id,) in course_ids:
                    self.course_sync.sync_single_course(course_id)
                
                logger.info(f"Synced {len(course_ids)} courses for category {category_id}")
        except Exception as e:
            logger.error(f"Error handling category change: {str(e)}")
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()